package org.example.utils

import org.example.pojo.{HotItem, HotKeyword, HotTotal}

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

// MySQL连接工具类
object mysqlConn {
  // 数据库连接信息
  val url = "jdbc:mysql://8.138.95.218:3306/hot_analysis"
  val driver = "com.mysql.jdbc.Driver"
  val username = "hot_analysis"
  val password = "123456"

  var connection: Connection = null

  def getConnection(): Unit = {
    Class.forName(driver)
    connection = DriverManager.getConnection(url, username, password)
  }

  /**
   * 批量插入或更新数据
   *
   * @param dataList 要插入或更新的数据列表
   */
  def batchUpsert(dataList: List[HotItem]): Unit = {
    val batchSize = 1000 // 批量操作的大小
    val batches = dataList.grouped(batchSize).toList // 将数据列表按照 batchSize 分组

    // 遍历每个分组，执行批量插入或更新操作
    for (batch <- batches) {
      val values = batch.map(data => s"('${data.platform}', ${data.count}, '${data.hot_keyword}', '${data.start_time}', '${data.end_time}', ${data.hot_top}, ${data.top_ranking})").mkString(",")
      val upsertSql =
        s"""
           |INSERT INTO analysis (platform, top_count, hot_keyword, start_time, end_time, hot_top, top_ranking)
           |VALUES $values
           |ON DUPLICATE KEY UPDATE
           |top_count = VALUES(count),
           |end_time = VALUES(end_time),
           |hot_top = VALUES(hot_top),
           |top_ranking = VALUES(top_ranking)
        """.stripMargin
      insert(upsertSql)
    }
  }

  /**
   * 批量插入或更新数据
   *
   * @param dataList 要插入或更新的数据列表
   */
  def batchUpsertTotal(dataList: List[HotTotal]): Unit = {
    val batchSize = 1000 // 批量操作的大小
    val batches = dataList.grouped(batchSize).toList // 将数据列表按照 batchSize 分组

    getConnection()

    // 遍历每个分组，执行批量插入或更新操作
    for (batch <- batches) {
      val values = batch.map(data => s"(${data.total}, '${data.platform}', '${data.time}')").mkString(",")
      val upsertSql =
        s"""
           |INSERT INTO hot_total (total, platform, time)
           |VALUES $values
           |ON DUPLICATE KEY UPDATE
           |total = VALUES(total)
      """.stripMargin

      val statement: PreparedStatement = connection.prepareStatement(upsertSql)
      statement.executeUpdate()
      statement.close()
    }

    closeConnection()
  }

  /**
   * 插入或更新数据
   *
   * @param sql 插入或更新的SQL语句
   * @return
   */
  def insert(sql: String): Int = {
    val statement = connection.createStatement()
    val result = statement.executeUpdate(sql)
    statement.close()
    result
  }

  /**
   * 关闭数据库连接
   */
  def closeConnection(): Unit = {
    if (connection != null) {
      connection.close()
    }
  }
}
